package com.pokermind.dse.tcp.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;

/**
 * 数据库配置类 - TCP服务专用
 * 基于DSE的配置，针对TCP服务器进行优化
 * 
 * @author PokerMind Team
 */
@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

    private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(DatabaseConfig.class);

    @Value("${pokermind.database.init-on-startup:true}")
    private boolean initOnStartup;

    @Value("${pokermind.database.execute-schema:true}")
    private boolean executeSchema;

    /**
     * JdbcTemplate Bean配置 - TCP优化版本
     */
    @Bean
    @Primary
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        logger.info("Initializing JdbcTemplate - initOnStartup: {}", initOnStartup);
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // TCP服务器优化设置
        jdbcTemplate.setQueryTimeout(15);  // TCP服务要求更快的响应
        jdbcTemplate.setMaxRows(5000);     // 适中的行数限制
        jdbcTemplate.setFetchSize(200);    // 更大的获取大小，提高批量性能

        // 启动时初始化数据库连接
        logger.info("initOnStartup value: {} - will {}initialize database", 
            initOnStartup, initOnStartup ? "" : "NOT ");
        
        if (initOnStartup) {
            logger.info("Calling initializeDatabase...");
            initializeDatabase(dataSource);
        } else {
            logger.warn("Database initialization skipped (initOnStartup=false)");
        }

        logger.info("JdbcTemplate configured for TCP service with optimized settings");
        return jdbcTemplate;
    }

    /**
     * 事务管理器配置 - TCP优化版本
     */
    @Bean
    @Primary
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);

        // TCP服务器优化设置
        transactionManager.setDefaultTimeout(30);  // 较短的事务超时
        transactionManager.setValidateExistingTransaction(true);

        return transactionManager;
    }

    /**
     * 初始化数据库连接 (MySQL) - TCP服务专用
     */
    private void initializeDatabase(DataSource dataSource) {
        try (Connection connection = dataSource.getConnection(); 
             Statement statement = connection.createStatement()) {

            // 显示数据库连接信息
            String dbUrl = connection.getMetaData().getURL();
            String dbName = connection.getCatalog();
            logger.info("========================================");
            logger.info("Database Connection Info:");
            logger.info("  URL: {}", dbUrl);
            logger.info("  Database: {}", dbName);
            logger.info("  User: {}", connection.getMetaData().getUserName());
            logger.info("========================================");

            // MySQL TCP服务器优化参数
            statement.execute(
                "SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
            statement.execute("SET SESSION time_zone = '+08:00'");
            statement.execute("SET SESSION autocommit = 1");
            
            // TCP服务器特有设置
            statement.execute("SET SESSION wait_timeout = 3600");        // 1小时超时
            statement.execute("SET SESSION interactive_timeout = 3600");  // 1小时超时
            statement.execute("SET SESSION net_read_timeout = 60");       // 读取超时
            statement.execute("SET SESSION net_write_timeout = 60");      // 写入超时

            // 验证核心表存在
            if (tableExists(connection, "poker_users") && 
                tableExists(connection, "poker_hands") && 
                tableExists(connection, "poker_hand_actions")) {
                
                logger.info("✓ MySQL Database connected successfully - Core tables verified");
            } else {
                logger.warn("⚠ MySQL Database connected but some core tables missing");
            }

        } catch (Exception e) {
            logger.error("✗ Failed to initialize MySQL database: {}", e.getMessage(), e);
            throw new RuntimeException("MySQL Database initialization failed", e);
        }
    }

    /**
     * 检查表是否存在 (MySQL)
     */
    private boolean tableExists(Connection connection, String tableName) {
        try (Statement statement = connection.createStatement()) {
            var resultSet = statement.executeQuery(
                "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " + 
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'");
            return resultSet.next();
        } catch (Exception e) {
            logger.warn("Error checking table existence for {}: {}", tableName, e.getMessage());
            return false;
        }
    }
}
